﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using OfficeOpenXml;
using System.IO;
using System.Diagnostics;
using OfficeOpenXmlCrypto;
using System.Globalization;

namespace MoneyWentWhere.DataModel.Storage
{
    class Excel2007Format : DataFormatX, IHasPassword
    {
        static readonly String TabPrefix = Char.ConvertFromUtf32(0x25AA);
        const String DateFormat = "yyyy-MM-ddThh:mm:ss";
        const int MwwStartRow = 10;

        const String ColId = "ID#";

        const String TabTransfers = "Transfers";

        public String Password { get; set; } 

        public override void Save(MoneyDataSet ds, string filename)
        {
            ds.Filename = filename;
            ds.DataFormat = this;

            OfficeCryptoStream stream = ds.ExtendedFileData as OfficeCryptoStream;
            if (stream == null)
            {
                stream = OfficeCryptoStream.Create(filename);
                stream.Password = Password;
            }
            stream.Seek(0, SeekOrigin.Begin);

            using (ExcelPackage package = new ExcelPackage(stream))
            {
                ExcelWorksheets doc = package.Workbook.Worksheets;
                WriteMWW(doc, ds);
                WriteAccountTabs(doc, ds);

                package.Save();
            }
            stream.SaveAs(filename);

            ds.ExtendedFileData = stream;
            // NOT closing the stream, that should 
            // be done when MoneyDataSet is disposed
        }


        public override MoneyDataSet Load(string filename)
        {
            MoneyDataSet ds = new MoneyDataSet();

            OfficeCryptoStream stream;
            try
            {
                stream = OfficeCryptoStream.Open(filename, Password);
            }
            catch (OfficeOpenXmlCrypto.InvalidPasswordException e)
            {
                // Re-throw inside our own password exception
                throw new InvalidPasswordException("Invalid password.", e);
            }

            using (ExcelPackage package = new ExcelPackage(stream))
            {
                ExcelWorksheets doc = package.Workbook.Worksheets;
                ReadMWW(doc, ds);
                ReadAccountTabs(doc, ds);
            }


            ds.ExtendedFileData = stream;
            ds.Filename = filename;
            ds.DataFormat = this;
            ds.Password = Password;
            return ds;
        }

        void WriteMWW(ExcelWorksheets doc, MoneyDataSet ds)
        {
            ExcelWorksheet mwwWs = doc.GetOrCreateWorksheet("MWW");
            mwwWs.Hidden = true;

            int row = MwwStartRow;
            mwwWs.WriteRow(row++, "Application", "MoneyWentWhere");
            mwwWs.WriteRow(row++, "Version", "1.0");
            mwwWs.WriteRow(row++, "DefaultCurrency", ds.DefaultCurrency);
            // nextRefNum
        }
        void ReadMWW(ExcelWorksheets doc, MoneyDataSet ds)
        {
            ExcelWorksheet ws = doc["MWW"];
            if (ws == null) { return; }

            // Build a dict of values
            Dictionary<String, String> dict = new Dictionary<string, string>();
            int row = MwwStartRow;
            while (true)
            {
                if (ws.RowIsEmpty(row, 1)) { break; }
                dict.AddOrReplace(
                    ws.Cell(row, 1).Value,
                    ws.Cell(row, 2).Value);
                ++row;
            }

            // Assign values
            ds.DefaultCurrency = new Currency("USD");
            ds.Currencies.Add(ds.DefaultCurrency);

            // nextRefNum
        }

        void WriteAccountTabs(ExcelWorksheets doc, MoneyDataSet ds)
        {
            DeleteAllAccountTabs(doc, ds);

            foreach (Account ac in ds.Accounts) 
            {
                // Tx info
                String[] columns = new String[] {
                    ColId, 
                    "Date", 
                    "Amount", 
                    "Comment", 
                    "Category", 
                    "Kind",
                    "TransferToTx",
                    "TxSettings"};

                // TODO: consider passing in a number of rows to improve perf
                ExcelWorksheet txWs = doc.Add(TabPrefix + ac.Name,
                    4 + ac.Transactions.Count, columns.Length);
                int row = 1;
                
                // Account info
                txWs.WriteRow("WsTitle", row++, ac.Name); // Decorative only
                txWs.WriteRow(row++, "GUID: ", ac.Guid);
                txWs.WriteRow(row++, "Currency: ", ac.Currency);
                txWs.WriteRow("TxHeader", row++, columns); 

                foreach (Transaction tx in ac.Transactions)
                {
                    String ttTxGuid = tx.TransferToTx == null ?
                        "" : tx.TransferToTx.Guid.ToString();

                    txWs.WriteRow(row,
                        tx.Guid, 
                        tx.Date.ToOADate(),
                        tx.Amount, 
                        tx.Comment,
                        tx.Category,
                        tx.Kind,
                        ttTxGuid,
                        ""); // Special *import-only* column
                    ++row;
                }

                // Delete remaining tx -- no need, page is new,
                // but would need this if we were editing a sheet
                // txWs.DeleteRemainingRows(row, columns.Length);
            }
        }

        void DeleteAllAccountTabs(ExcelWorksheets doc, MoneyDataSet ds)
        {
            for (int i = 0; i < doc.Count; i++)
            {
                int posId = i + 1;
                String wsName = doc[posId].Name;
                if (wsName.StartsWith(TabPrefix))
                {
                    // If no account delete the tab
                    doc.Delete(posId);
                    --i; // move back so iteration won't skip tabs
                }
            }
        }

        void ReadAccountTabs(ExcelWorksheets doc, MoneyDataSet ds)
        {
            Dictionary<Transaction, TxSettings> importTxSettings
                = new Dictionary<Transaction, TxSettings>();

            for (int i = 0; i < doc.Count; i++)
            {
                ExcelWorksheet ws = doc[i + 1];
                if (ws.Name.StartsWith(TabPrefix))
                {
                    String acName = ws.Name.Substring(TabPrefix.Length);

                    int row = 1;
                    row++; // Skip over decorative title

                    Guid guid = Guid.Empty;
                    if (ws.Cell(row, 1).Value.StartsWith("GUID:"))
                    {
                        Guid_TryParse(ws.Cell(row++, 2).Value, out guid);
                    }

                    Currency curr = ds.DefaultCurrency;
                    if (ws.Cell(row, 1).Value.StartsWith("Currency:"))
                    {
                        curr = new Currency(ws.Cell(row++, 2).Value);
                    }

                    Account ac = new Account(ds, guid, acName);
                    ds.Accounts.Add(ac);

                    ac.Currency = curr;

                    // Find the header row
                    while (ws.Cell(row++, 1).Value != ColId) { }

                    // Read transactions
                    while (true)
                    {
                        if (ws.RowIsEmpty(row, 4)) { break; }

                        CreateTransaction(ac, row, ws, importTxSettings);
                        ++row;
                    }
                }
            }

            // Apply import settings
            foreach (Transaction tx in importTxSettings.Keys)
            {
                tx.Settings = importTxSettings[tx];
            }
        }

        void CreateTransaction(Account ac, int row, ExcelWorksheet ws,
            Dictionary<Transaction, TxSettings> importSettings)
        {
            int col = 1;

            // Id must be the first column.
            Guid guid = Guid.Empty;
            Guid_TryParse(ws.Cell(row, col++).Value, out guid);

            DateTime date;
            if (!Date_TryParse(ws.Cell(row, col++).Value, out date))
            {
                date = DateTime.Now;
            }
            
            decimal amount = 0;
            decimal.TryParse(ws.Cell(row, col++).Value, out amount);

            String comment = ws.Cell(row, col++).Value;

            String category = ws.Cell(row, col++).Value;

            TransactionKind kind;
            String kindStr = ws.Cell(row, col++).Value;
            if (kindStr == "Transfer") { kind = TransactionKind.Transfer; }
            else if (kindStr == "Reconcile") { kind = TransactionKind.Reconcile; }
            else { kind = TransactionKind.Normal; }

            //EnumX.TryParse<TransactionKind>(ws.Cell(row, col++).Value, out kind);

            Guid transferToTxGuid = Guid.Empty;
            Guid_TryParse(ws.Cell(row, col++).Value, out transferToTxGuid);

            Transaction tx = new Transaction(guid, ac, kind, date, 
                transferToTxGuid, amount, comment, category);
            ac.Transactions.Add(tx);

            // Import settings
            String settingsRep = ws.Cell(row, col++).Value.Trim();
            if (settingsRep != "") 
            { 
                importSettings.Add(tx, TxSettings.Parse(settingsRep)); 
            } 

        }

        bool Date_TryParse(String rep, out DateTime date)
        {
            date = DateTime.MinValue;

            // OA date is default
            double oaDate;
            if (double.TryParse(rep, out oaDate))
            {
                date = DateTime.FromOADate(oaDate);
                return true;
            }

            // ISO date is desired
            if (DateTime.TryParseExact(rep, DateFormat,
                    CultureInfo.InvariantCulture, DateTimeStyles.None, out date))
            {
                return true;
            }

            // Try to extract any date
            if (DateTime.TryParse(rep, out date))
            {
                return true;
            }

            return false;
        }

        bool Guid_TryParse(String rep, out Guid guid)
        {
            guid = Guid.Empty;
            
            rep = rep.Trim();
            if (String.IsNullOrEmpty(rep)) { return true; }

            try
            {
                guid = new Guid(rep); 
            }
            catch (FormatException)
            {
                // Cannot parse Guid
                return false;
            }
            return true;
        }

        public override void Save(MoneyDataSet ds, Stream outStream)
        {
            throw new InvalidOperationException("Not supported in Excel format.");
        }

        public override MoneyDataSet Load(Stream inStream)
        {
            throw new InvalidOperationException("Not supported in Excel format.");
        }

        public override bool AcceptsFile(string filename)
        {
            if (!File.Exists(filename))
            {
                return Path.GetExtension(filename).Equals(".xlsx", 
                    StringComparison.InvariantCultureIgnoreCase);
            }
            return true;
        }

    }
}
